﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            //M2M_Create();
            //M2M_Delete();
            //One2M_Query_Join();
            //M2M_Query_Join();
            //One2M_Create();
            One2M_Delete();
        }

        #region One to Many (Region - Territorries)

        public static void One2M_Create()
        {
            Entities3 db = new Entities3();
            Region reg = new Region { RegionID = 1001, RegionDescription = "RED_DESC" };
            db.AddToRegion(reg);

            Territories tr1 = new Territories { TerritoryID = "111", TerritoryDescription = "11_DESC" };
            //db.AddToTerritories(tr1);
            reg.Territories.Add(tr1);
            Territories tr2 = new Territories { TerritoryID = "112", TerritoryDescription = "12_DESC", Region = reg };
            db.AddToTerritories(tr2);
            //reg.Territories.Add(tr2);

            db.SaveChanges();
            
        }

        public static void One2M_Delete()
        {
            Entities3 db = new Entities3();

            var tr = db.Territories.First(t => t.TerritoryID == "111");
            //// Get the subscription you want to delete
            //var reg = db.Region.First(r => r.RegionID == 1001);
            //reg.Territories.Attach(tr);// Attach it (the ObjectContext now 'thinks' it belongs to the topic)
            //reg.Territories.Remove(tr);
            
            db.DeleteObject(tr);
            
            db.SaveChanges();
        }

        public static void One2M_Query_Join()
        {
            Entities3 db = new Entities3();
            var regionTerritorries = (from r in db.Region
                                      join t in db.Territories
                                      on r.RegionID equals t.Region.RegionID
                                      select new { reDes = r.RegionDescription, teDes = t.TerritoryDescription }
                                          );
            foreach (var rT in regionTerritorries)
            {

                Console.WriteLine(rT.reDes.Trim() + " Region - Terr. " + rT.teDes);
            }
        }

        #endregion

        #region Many to Many Employee - Territories

        public static void M2M_Create()
        {
            Entities3 db = new Entities3();

            Employees emp = new Employees {  FirstName = "Ashraful", LastName = "Alam"};
            db.AddToEmployees(emp);

            Region region = new Region { RegionID = 100, RegionDescription = "REG_1_DESC" };
            db.AddToRegion(region);

            Territories tr = new Territories { TerritoryID = "111", TerritoryDescription = "11_DESC", Region = region };
            db.AddToTerritories(tr);

            //emp.Territories.Add(tr);
            tr.Employees.Add(emp);//both works same
                                    
            db.SaveChanges();
        }

        public static void M2M_Delete()
        {
            Entities3 db = new Entities3();

            var tr = db.Territories.First(t => t.TerritoryID == "111");
            // Get the subscription you want to delete
            var emp = db.Employees.First(e => e.FirstName == "Ashraful");
            emp.Territories.Attach(tr);// Attach it (the ObjectContext now 'thinks' it belongs to the topic)
            emp.Territories.Remove(tr);
            db.SaveChanges();
        }

        public static void M2M_Query_Join()
        {
            Entities3 db = new Entities3();
            var employeeTerritoriesJoin = (from e in db.Employees
                                            from t in e.Territories
                                            select new { eFirstName = e.FirstName, eLastName = e.LastName, teDes = t.TerritoryDescription }
                                            );
            Console.WriteLine("---" + employeeTerritoriesJoin.Count());

            foreach (var e in employeeTerritoriesJoin)
            {

                Console.WriteLine(e.eFirstName + " " + e.eLastName + " Name - Terri. " + e.teDes);
            }
        }

        #endregion

    }
}
